﻿Imports System.Data.OleDb
Module XL_TaiKhoan
    Structure TaiKhoan
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _tenTK As String
        Property TenTK() As String
            Get
                Return _tenTK
            End Get
            Set(ByVal value As String)
                _tenTK = value
            End Set
        End Property
        Private _nguoiDung As Integer
        Property NguoiDung() As Integer
            Get
                Return _nguoiDung
            End Get
            Set(ByVal value As Integer)
                _nguoiDung = value
            End Set
        End Property

        Private _soDu As Integer
        Property SoDu() As Integer
            Get
                Return _soDu
            End Get
            Set(ByVal value As Integer)
                _soDu = value
            End Set
        End Property

        Private _laiSuat As Double
        Property LaiSuat() As Double
            Get
                Return _laiSuat
            End Get
            Set(ByVal value As Double)
                _laiSuat = value
            End Set
        End Property

        Private _trangThai As Integer
        Property TrangThai() As Integer
            Get
                Return _trangThai
            End Get
            Set(ByVal value As Integer)
                _trangThai = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal nd As Integer) As List(Of TaiKhoan)
        Try
            Dim ds As New List(Of TaiKhoan)
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from TaiKhoan where NguoiDung=@nd and TrangThai=1", con)
            cmd.Parameters.AddWithValue("@nd", nd)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As TaiKhoan = New TaiKhoan()
                p.ID = rd.GetInt32(0)
                p.TenTK = rd.GetString(1)
                p.NguoiDung = rd.GetInt32(2)
                p.SoDu = rd.GetInt32(3)
                p.LaiSuat = rd.GetDouble(4)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return New List(Of TaiKhoan)
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As TaiKhoan
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from TaiKhoan where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As TaiKhoan = New TaiKhoan()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.TenTK = rd.GetString(1)
                p.NguoiDung = rd.GetInt32(2)
                p.SoDu = rd.GetInt32(3)
                p.LaiSuat = rd.GetDouble(4)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function KTTenTK(ByVal tk As String, ByVal nd As Integer) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from TaiKhoan where NguoiDung=@nd and TenTK=@tentk", con)
            cmd.Parameters.AddWithValue("@nd", nd)
            cmd.Parameters.AddWithValue("@tentk", tk)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            If rd.Read() Then
                Return rd.GetInt32(0)
            Else
                Return 0
            End If
            con.Close()
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Function Them(ByRef p As TaiKhoan) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into TaiKhoan(TenTK,NguoiDung,SoDu,LaiSuat,TrangThai) values(@TenTK,@NguoiDung,@sd,@ls,@tt)", con)
            cmd.Parameters.AddWithValue("@TenTK", p.TenTK)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@sd", p.SoDu)
            cmd.Parameters.AddWithValue("@ls", p.LaiSuat)
            cmd.Parameters.AddWithValue("@tt", p.TrangThai)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@Identity"
            p.ID = cmd.ExecuteScalar()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As Integer) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()

            Dim cmd As OleDbCommand = New OleDbCommand("update TaiKhoan set TrangThai = 0 where ID = @tk", con)
            cmd.Parameters.AddWithValue("@tk", p)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat1(ByVal ten As String, ByVal ls As Double, ByVal tk As Integer) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update TaiKhoan set TenTK=@tk,LaiSuat=@ls where ID = @ID", con)
            cmd.Parameters.AddWithValue("@tk", ten)
            cmd.Parameters.AddWithValue("@ls", ls)
            cmd.Parameters.AddWithValue("@ID", tk)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat(ByVal tk As TaiKhoan) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update TaiKhoan set TenTK=@TenTK, NguoiDung=@NguoiDung, SoDu=@SoDu, LaiSuat=@LaiSuat where ID = @ID", con)
            cmd.Parameters.AddWithValue("@TenTK", tk.TenTK)
            cmd.Parameters.AddWithValue("@NguoiDung", tk.NguoiDung)
            cmd.Parameters.AddWithValue("@SoDu", tk.SoDu)
            cmd.Parameters.AddWithValue("@LaiSuat", tk.LaiSuat)
            cmd.Parameters.AddWithValue("@ID", tk.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function
End Module
